package data.database;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import data.database.ClusterTable.ClusterTableRecord;

import util.EditDistance;
import video.local.analyse.Video;

public class StereoTable{
	private DatabaseConnection dc;
	private String tableName;
	private float stereoSimilarityThreshold;
	
	public class StereoDatabaseRecord{
		public String id;
		public String title;
		public String similar;
	}
	
	public StereoTable(DatabaseConnection dc, String tableName){
		this.dc = dc;
		this.tableName = tableName;
		this.stereoSimilarityThreshold = 1;
	}
	
	public StereoTable(DatabaseConnection dc, String tableName, float stereoSimilarityThreshold){
		this.dc = dc;
		this.tableName = tableName;
		this.stereoSimilarityThreshold = stereoSimilarityThreshold;
	}
	
	/**
	 * 
	 * @param vr
	 * @return add success or not
	 */
	public void addData(VideoRecord vr){
		String query = "INSERT IGNORE INTO "+tableName+" (url, title) VALUES (\""+vr.url+"\", \""+vr.title+"\" )";
		dc.updataOneRecord(query);
	}
	
	/**
	 * text cluster
	 * @param threshold
	 * @return
	 */
	public ArrayList<ClusterTableRecord> textCluster(double threshold) {
		ArrayList<StereoDatabaseRecord> records = new ArrayList<StereoDatabaseRecord>();
		String query = "SELECT id,title FROM "+tableName;
		ResultSet rs = dc.executeQuery(query);
		
		try {
			while(rs.next()){
				StereoDatabaseRecord record = new StereoDatabaseRecord();
				record.id = rs.getString("id");
				record.title = rs.getString("title");
				
				records.add(record);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		//to cluster
		ArrayList<ClusterTableRecord> clusterRecords = new ArrayList<ClusterTableRecord>();
		for(int i=0; i<records.size(); i++){
			for(int j=i+1; j<records.size(); j++){
				float simi = EditDistance.getSimilartiy(records.get(i).title, records.get(j).title);
				if(simi > threshold){
					ClusterTableRecord clusterRecord = new ClusterTableRecord();
					clusterRecord.id1 = records.get(i).id;
					clusterRecord.id2 = records.get(j).id;
					clusterRecord.txtsimi = simi;
						
					clusterRecords.add(clusterRecord);
				}				
			}
		}
		return clusterRecords;
	}
	
	/**
	 * in fact, this function is for class ClusterTable only
	 * @param id
	 * @return
	 */
	public String getUrlById(String id){
		String query = "SELECT * FROM "+tableName+" WHERE id="+id;
		ResultSet rs = dc.executeQuery(query);
		try {
			while(rs.next())
				return rs.getString("url");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

}
